import datetime
import os
import string

from flask import Flask, render_template, request, session, url_for
from werkzeug.utils import redirect
from apps.user import User  # 连接数据库 中的 User表 和 Money表
from apps.social import Social

# from money import Money

app = Flask(__name__)
app.secret_key = os.urandom(38)  # 通过os随机生成session密码放在app里面
# 变量

C = 0  # 社团总人数
# M = 0  # 社团总余额
# 链接 数据库服务器

import MySQLdb

db = MySQLdb.connect(host="localhost", user="root", password="", database="test", charset="utf8")
cursor = db.cursor()  # 使用cursor()方法 连接数据库
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:@localhost:3306/test'

# 设置文件上传保存路径
app.config['UPLOAD_FOLDER'] = 'static/upload/'
# MAX_CONTENT_LENGTH设置上传文件的大小，单位字节
app.config['MAX_CONTENT_LENGTH'] = 1 * 1024 * 1024


@app.route('/')
def index():
    # 写一个测试代码来验证是否连接成功

    # engine = db.get_engine()
    # with engine.connect() as conn:
    #     result = conn.execute("select 1")
    #     print(result.fetchone())

    # return render_template("login.html")
    return render_template("front_home.html")


@app.route("/front_home")
def front_home():
    return render_template("front_home.html")


@app.route("/front_Community_display")
def front_Community_display():
    return render_template("front_Community_display.html")


@app.route("/front_Wonderful_moments")
def front_Wonderful_moments():
    return render_template("front_Wonderful_moments.html")


# @app.route("/front_dynamic-blog")
# def front_dynamic():
#     return render_template("front_dynamic-blog.html")


@app.route("/front_Message_Board")
def front_Message_Board():
    return render_template("front_Message_Board.html")


@app.route("/front_dynamic")
def front_dynamic():
    return render_template("front_dynamic.html")


@app.route("/front_contact")
def front_contact():
    return render_template("front_contact.html")


@app.route("/login")
def login():
    return render_template("/login.html")


global USERNAME
USERNAME = ""

global I
I = 0

global UID
UID = 0


@app.route("/gologin", methods=["POST"])
def gologin():
    # 做登陆校验（判断用户名和密码是否正确）
    username = request.form.get('username')  # 从form表单中 得到 name为username username指向它
    password = request.form.get('password')

    sql = "select * from user where username = %s and password = %s"  # sql语句用于在数据库遍历信息
    i = cursor.execute(sql, [username, password])  # 执行数据库操作 execute(sql,[参数1, 参数2]) 返回值为 0 代表这条语句没有结果 ！=0 就代表有结果
    # print(i) i = 1;
    # db.commit()

    # 此语句进行分流 若 role 为student 就转到 student对应的主页 不然就 转到 teacher对应的主页
    sql = "select * from user where username = %s and password = %s and role = %s "
    o = cursor.execute(sql, [username, password, "teacher"])
    db.commit()
    # print(o)
    # user = cursor.fetchall()
    if i != 0:
        if o != 0:
            global USERNAME
            global UID
            USERNAME = username
            print(USERNAME)
            sql = "select id from user where username = %s"
            cursor.execute(sql, USERNAME)
            db.commit()
            id = cursor.fetchone()
            for i in id:
                UID = i
            return redirect(url_for("indexTeacher", username=username))  # 重定向  重新定位到template 一般不用于前端页面的跳转
        # return redirect("/template")
        else:
            USERNAME = username
            print(USERNAME)
            sql = "select id from user where username = %s"
            cursor.execute(sql, USERNAME)
            db.commit()
            id = cursor.fetchone()
            for i in id:
                UID = i
            return redirect(url_for('indexStudent', username=username))
    return render_template("login.html")  # 若密码不一致就重新登录


@app.route("/indexTeacher")
def indexTeacher():
    username = USERNAME
    # 遍历社团列表
    global C
    socialList = []
    sql = "select * from social"
    cursor.execute(sql)
    db.commit()
    socials = cursor.fetchall()
    print(socials)
    for social in socials:
        # social = social(social[0], social[1], social[2], social[3], social[4], social[5])
        C = C + 1
        socialList.append(social)
    print(C)
    print(socialList)
    i = C
    I = i
    # 遍历社团总数
    return render_template("indexTeacher.html", socials=socialList, username=username)


@app.route("/indexStudent")
def indexStudent():
    username = USERNAME
    print(username)
    print("UID=")
    print(UID)
    # 查询个人信息
    sql = "select * from user where username = %s"
    cursor.execute(sql, [username])  # 1
    db.commit()
    user = cursor.fetchall()  # ((),(),())
    print(user)
    academy = user[0][5]
    social = user[0][6]
    credit = user[0][7]
    return render_template("indexStudent.html", users=user, username=username, academy=academy, social=social,
                           credit=credit)


@app.route("/teacherSocialManage")
def teacherSocialManage():
    # 遍历社团列表
    global C
    username = USERNAME
    socialList = []
    sql = "select * from social"
    cursor.execute(sql)
    db.commit()
    socials = cursor.fetchall()  # ((),(),())
    print(socials)
    num = 0
    for social in socials:
        # social = Social(social[0], social[1], social[2], social[3], social[4], social[5], social[6])
        C = C + 1
        num += int(social[3])
        socialList.append(social)  # [(),(),()]
    print(C)
    print(socialList)
    i = C
    C = 0
    sql = "SELECT count(*) from activity"
    cursor.execute(sql)
    db.commit()
    a = cursor.fetchall()
    print(a[0][0])
    activityAllNum = a[0][0]

    sql = "SELECT sum(activityMoney) from activity"
    cursor.execute(sql)
    db.commit()
    a = cursor.fetchall()
    print(a[0][0])
    activityAllMoney = a[0][0]
    # 遍历社团总数
    return render_template("teacherSocialManage.html", socials=socialList, i=i, num=num, activityAllNum=activityAllNum,
                           activityAllMoney=activityAllMoney, username=username)


@app.route("/teacher_Managenent_Add")
def teacher_Managenent_Add():
    username = USERNAME
    now = datetime.datetime.now()
    print('当前系统日期和时间是:')
    time = now.strftime("%Y-%m-%d")
    return render_template('teacher_Managenent_Add.html', username=username, time=time)


# 添加社团信息
@app.route("/goTeacher_Managenent_Add", methods=["POST"])
def goTeacher_Managenent_Add():
    name = request.form.get('name')
    manager = request.form.get('manager')
    people = request.form.get('people')
    foundTime = request.form.get('foundTime')
    type = request.form.get('type')
    print(name)
    print(manager)
    sql = 'insert into social (name,manager,people,foundTime,type) values(%s,%s,%s,%s,%s)'
    cursor.execute(sql, [name, manager, people, foundTime, type])  # sql 语句 和 数据列表 使用 execute 方法进行操作
    db.commit()
    print("添加成功")
    return redirect("/teacherSocialManage")


updateList = []


# 修改信息
@app.route("/teacher_Managenent_Update/<id>/<name>/<manager>/<people>/<foundTime>/<type>")  # 直接传参数进来
def teacher_Managenent_Update(id, name, manager, people, foundTime, type):
    username = USERNAME
    return render_template('teacher_Managenent_Update.html', name=name, manager=manager, people=people,
                           foundTime=foundTime,
                           type=type, id=id, username=username)


@app.route("/goTeacher_Managenent_Update", methods=["POST"])
def goTeacher_Managenent_Update():
    id = request.form.get('id')
    name = request.form.get('name')  # 从form表单获取
    manager = request.form.get('manager')
    people = request.form.get('people')
    foundTime = request.form.get('foundTime')
    type = request.form.get('type')
    print(id, name, manager, people, foundTime, type)
    sql = "update social set name=%s,manager=%s,people=%s,foundTime=%s,type=%s where id=%s"
    cursor.execute(sql, [name, manager, people, foundTime, type, id])  # 和上面sql对应
    db.commit()  # 做一个提交操作
    return redirect("/teacherSocialManage")


# 删除社团信息
@app.route("/socialDelete/<name>")  # <id> 用来接收/delete/    {{user.id}}这条语句传输过来的{{user.id}}
def socailDelete(name):
    print(name)
    sql = "delete from social where name = %s"  # 以社团名字 name 来作为删除的标记
    cursor.execute(sql, [name])
    db.commit()
    return redirect("/teacherSocialManage")


# 查询
@app.route("/socialSelect", methods=["POST"])
def socialSelect():
    socialList = []
    name = request.form.get('name')
    print(name)
    sql = f"select * from social where name like '%{name}%'"  # 模糊查询
    print(sql)
    # cursor.execute(sql,[name])
    # socials = cursor.fetchall()
    # db.commit()
    # print(socials)
    # return render_template("socialmanage.html", socials=socials)

    i = cursor.execute(sql)
    Socials = cursor.fetchall()  # Socials 获取sql语句执行后的结果
    print(i)
    print(Socials)
    if i != 0:
        for social in Socials:
            # social = social[social[0], social[1], social[2], social[3], social[4], social[5], social[6]]
            socialList.append(social)
        print(socialList)
        return render_template('teacherSocialManage.html', socials=socialList)
    else:
        return render_template('teacherSocialManage.html')

    # social = Social.query.filter(Social.content.like("%" + name + "%") if name is not None else "").all()
    # socialList.append(social)
    # return render_template('socialmanage.html', Socials=socialList)


@app.route("/teacher_Social_People/<socialId>/<socialName>")
def teacher_Social_People(socialId, socialName):
    print(socialId)
    sql = "select UID from us where sid=%s"
    cursor.execute(sql, socialId)
    db.commit()
    userList = []
    for i in cursor.fetchall():
        sql = "select * from user where id=%s"
        cursor.execute(sql, i[0])
        db.commit()
        userList.append(cursor.fetchone())
    print(userList)
    return render_template("teacher_Social_People.html", users=userList, socialName=socialName)


@app.route("/teacher_Activity_Manage")
def teacher_Activity_Manage():
    # 遍历activity列表
    username = USERNAME
    activityList = []
    now = datetime.datetime.now()
    print('当前系统日期和时间是:')
    time = now.strftime("%Y-%m-%d")
    sql = f"select * from activity where activityEndTime >= '{time}' order by activityStartTime asc"
    print(sql)
    cursor.execute(sql)
    db.commit()
    activitys = cursor.fetchall()  # ((),(),())
    print(activitys)
    for activity in activitys:
        # social = Social(social[0], social[1], social[2], social[3], social[4], social[5], social[6])

        activityList.append(activity)  # [(),(),()]
    print(activityList)

    return render_template("teacher_Activity_Manage.html", activitys=activityList, username=username)


@app.route("/teacher_Activity_Issuance")
def teacher_Activity_Issuance():
    now = datetime.datetime.now()
    print('当前系统日期和时间是:')
    time = now.strftime("%Y-%m-%d")
    username = USERNAME
    return render_template('teacher_Activity_Issuance.html', username=username, time=time)


@app.route("/teacher_Activity_Approval")
def teacher_Activity_Approval():
    username = USERNAME
    applyActivityList = []
    a = '"未处理"'
    sql = f" select * from applyActivity where successful = {a}"
    print(sql)
    cursor.execute(sql)
    db.commit()
    applyActivities = cursor.fetchall()
    for applyActivity in applyActivities:
        applyActivityList.append(applyActivity)
    return render_template("teacher_Activity_Approval.html", username=username, activitys=applyActivityList)


# successful : 已同意
@app.route("/teacher_Activity_Approval_Agree/<activityId>/<activityName>/<organizer>/<activityMoney"
           ">/<activityStartTime>/<activityEndTime>/<activitySite>/<activityGrade>/<activityType>/<activityDetail>")
def teacher_Activity_Approval_Agree(activityId, activityName, organizer, activityMoney, activityStartTime,
                                    activityEndTime, activitySite, activityGrade, activityType, activityDetail):
    # 把successful设 同意
    a = '"已同意"'
    sql = f"update applyactivity set successful = {a} where activityId=%s"
    print(sql)
    cursor.execute(sql, activityId)
    db.commit()

    # 把活动信息录入到activity表
    sql = "insert into activity (activityName,organizer,activityMoney,activityStartTime,activityEndTime," \
          "activitySite,activityGrade,activityType,activityDetail) values(%s,%s,%s,%s,%s,%s,%s,%s,%s) "
    cursor.execute(sql, [activityName, organizer, activityMoney, activityStartTime, activityEndTime, activitySite,
                         activityGrade, activityType, activityDetail])
    db.commit()
    return redirect("/teacher_Activity_Approval")


# successful : 已拒绝
@app.route("/teacher_Activity_Approval_Reject/<activityId>")
def teacher_Activity_Approval_Reject(activityId):
    a = '"已拒绝"'
    sql = f"update applyactivity set successful = {a} where activityId=%s"
    cursor.execute(sql, activityId)
    db.commit()
    return redirect("/teacher_Activity_Approval")


@app.route("/teacher_Activity_Manage_RegisterNumber/<activityId>")
def teacher_Activity_Manage_RegisterNumber(activityId):
    username = USERNAME
    userList = []
    users = []
    sql = "select ua.UID from ua where Aid=%s"
    cursor.execute(sql, activityId)
    db.commit()
    ids = cursor.fetchall()
    print(ids)
    for id in ids:
        sql = "select * from user where id=%s"
        print(id)
        cursor.execute(sql, id[0])
        db.commit()
        user = cursor.fetchall()
        print(user)
        for a in user:
            users.append(a)
    for user in users:
        userList.append(user)
    print(userList)
    return render_template("teacher_Activity_Manage_RegisterNumber.html", username=username, users=userList)


# 添加社团信息
@app.route("/goTeacher_Activity_Issuance", methods=["POST"])
def goTeacher_Activity_Issuance():
    activityName = request.form.get('activityName')
    Organizer = request.form.get('Organizer')
    activityMoney = request.form.get('activityMoney')
    activityStartTime = request.form.get('activityStartTime')
    activityEndTime = request.form.get('activityEndTime')
    activitySite = request.form.get('activitySite')
    activityGrade = request.form.get('activityGrade')
    activityType = request.form.get('activityType')
    # activityFile = request.form.get('activityFile')
    activityDetail = request.form.get('activityDetail')
    sql = 'insert into activity (activityName,Organizer,activityMoney,activityStartTime,activityEndTime,activitySite,' \
          'activityGrade,' \
          'activityType,activityDetail)' \
          'values(%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    cursor.execute(sql, [activityName, Organizer, activityMoney, activityStartTime, activityEndTime, activitySite,
                         activityGrade, activityType, activityDetail])  # sql 语句 和 数据列表 使用 execute 方法进行操作
    db.commit()
    print("添加成功")

    # sql = 'create table activityName=%s' \
    #       '(' \
    #       'Sname varchar(255) primary key,' \
    #       'Sno varchar(255),' \
    #       'Sgrade varchar(255)' \
    #       ');'
    # cursor.execute(sql, [activityName])
    # db.commit()
    # print("添加人员表成功")
    return redirect("/teacher_Activity_Manage")


@app.route(
    '/teacher_Activity_Manage_Update/<activityId>/<activityName>/<Organizer>/<activityMoney>/<activityStartTime'
    '>/<activityEndTime>/<activitySite'
    '>/<activityGrade>/<activityDetail>/<participants>')  # 直接传参数进来
def teacher_Activity_Manage_Update(activityId, activityName, Organizer, activityMoney, activityStartTime,
                                   activityEndTime, activitySite,
                                   activityGrade, activityDetail, participants):
    username = USERNAME
    return render_template('teacher_Activity_Manage_Update.html', activityId=activityId, activityName=activityName,
                           Organizer=Organizer, activityMoney=activityMoney, activityStartTime=activityStartTime,
                           activityEndTime=activityEndTime, activitySite=activitySite, activityGrade=activityGrade,
                           activityDetail=activityDetail, participants=participants, username=username)


@app.route("/goTeacher_Activity_Manage_Update", methods=["POST"])
def goTeacher_Activity_Manage_Update():
    activityId = request.form.get('activityId')
    activityName = request.form.get('activityName')  # 从form表单获取
    Organizer = request.form.get('Organizer')
    activityMoney = request.form.get('activityMoney')
    activityStartTime = request.form.get('activityStartTime')
    activityEndTime = request.form.get('activityEndTime')
    activitySite = request.form.get('activitySite')
    activityGrade = request.form.get('activityGrade')
    activityType = request.form.get('activityType')
    activityDetail = request.form.get('activityDetail')
    # print(activityId, activityName, Organizer, activityMoney, activityTime, activitySite, activityGrade)
    sql = "update activity set activityName=%s,Organizer=%s,activityMoney=%s,activityStartTime=%s,activityEndTime=%s " \
          ",activitySite=%s ,activityGrade=%s ,activityType=%s ,activityDetail=%s where activityId=%s "

    cursor.execute(sql,
                   [activityName, Organizer, activityMoney, activityStartTime, activityEndTime, activitySite,
                    activityGrade, activityType, activityDetail, activityId])
    # 和上面sql对应
    db.commit()  # 做一个提交操作
    return redirect("/teacher_Activity_Manage")


@app.route("/teacher_Activity_Manage_Delete/<activityId>")
def teacher_Activity_Manage_Delete(activityId):
    sql = "delete from activity where activityId = %s"
    cursor.execute(sql, activityId)
    db.commit()
    return redirect("/teacher_Activity_Manage")


@app.route("/activityDelete/<activityName>")  # <id> 用来接收/delete/    {{user.id}}这条语句传输过来的{{user.id}}
def activityDelete(activityName):
    print(activityName)
    sql = "delete from activity where activityName = %s"  # 以社团名字 name 来作为删除的标记
    cursor.execute(sql, [activityName])
    db.commit()
    return redirect("/activityManage")


@app.route("/activitySelect", methods=["POST"])
def activitySelect():
    activityList = []
    name = request.form.get('name')
    print(name)
    sql = f"select * from activity where activityName like '%{name}%'"  # 模糊查询
    print(sql)
    # cursor.execute(sql,[name])
    # socials = cursor.fetchall()
    # db.commit()
    # print(socials)
    # return render_template("socialmanage.html", socials=socials)

    i = cursor.execute(sql)
    Activitys = cursor.fetchall()  # Socials 获取sql语句执行后的结果
    print(i)
    print(Activitys)
    username = USERNAME
    if i != 0:
        for activity in Activitys:
            # social = social[social[0], social[1], social[2], social[3], social[4], social[5], social[6]]
            activityList.append(activity)
        print(activityList)
        return render_template('teacher_Activity_Manage.html', activitys=activityList, username=username)
    else:
        return render_template('teacher_Activity_Manage.html', username=username)


@app.route("/teacher_Activity_Journal")
def teacher_Activity_Journal():
    username = USERNAME
    activityList = []
    print(username)
    now = datetime.datetime.now()
    time = now.strftime("%Y-%m-%d")
    sql = f"select * from activity where activityEndTime < '{time}'"
    cursor.execute(sql)
    db.commit()
    activitys = cursor.fetchall()
    credit = 0.0
    for activity in activitys:
        print(activity)
        num = float(activity[7])
        credit += num
        activityList.append(activity)  # [(),(),()]
    print(credit)

    sql = "update user set credit=%s where username=%s"
    cursor.execute(sql, [credit, username])
    db.commit()

    return render_template("teacher_Activity_Journal.html", username=username, activitys=activityList)


@app.route("/student_Activity_Detail/<activityId>")
def activityDetail(activityId):
    print(activityId)
    sql = "select * from activity where activityId = %s"
    cursor.execute(sql, [activityId])
    db.commit()
    Detail = cursor.fetchall()
    Detail = Detail[0][11]
    return render_template('student_Activity_Detail.html', Detail=Detail)


@app.route("/endActivity")
def endActivity():
    username = USERNAME
    activityList = []
    now = datetime.datetime.now()
    print('当前系统日期和时间是:')
    time = now.strftime("%Y-%m-%d")
    sql = f"select * from activity where activityEndTime < '{time}' order by activityEndTime desc"
    print(sql)
    cursor.execute(sql)
    db.commit()
    activitys = cursor.fetchall()  # ((),(),())
    print(activitys)
    for activity in activitys:
        # social = Social(social[0], social[1], social[2], social[3], social[4], social[5], social[6])

        activityList.append(activity)  # [(),(),()]
    print(activityList)

    return render_template('endActivity.html', activitys=activityList, username=username)


@app.route("/student_Activity_Apply")
def student_Activity_Apply():
    now = datetime.datetime.now()
    time = now.strftime("%Y-%m-%d")
    username = USERNAME
    return render_template("student_Activity_Apply.html", time=time, username=username)


@app.route("/goStudent_Activity_Apply", methods=["POST"])
def goStudent_Activity_Apply():
    userName = USERNAME
    print(userName)
    activityName = request.form.get('activityName')
    Organizer = request.form.get('Organizer')
    activityMoney = request.form.get('activityMoney')
    activityStartTime = request.form.get('activityStartTime')
    activityEndTime = request.form.get('activityEndTime')
    activitySite = request.form.get('activitySite')
    activityGrade = request.form.get('activityGrade')
    activityType = request.form.get('activityType')
    activityDetail = request.form.get('activityDetail')
    successful = "未处理"
    sql = 'insert into applyActivity (userName,activityName,Organizer,activityMoney,activityStartTime,' \
          'activityEndTime,activitySite,activityGrade,activityType,activityDetail,successful)' \
          'values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
    cursor.execute(sql,
                   [userName, activityName, Organizer, activityMoney, activityStartTime, activityEndTime, activitySite,
                    activityGrade, activityType, activityDetail, successful])  # sql 语句 和 数据列表 使用 execute 方法进行操作
    db.commit()
    print("添加成功")
    return redirect("student_Activity_Apply_Record")


@app.route("/student_Activity_Apply_Record")
def student_Activity_Apply_Record():
    username = USERNAME
    applyActivityList = []
    sql = "select * from applyActivity where userName=%s"
    cursor.execute(sql, username)
    db.commit()
    applyActivities = cursor.fetchall()
    for applyActivity in applyActivities:
        applyActivityList.append(applyActivity)
    return render_template("student_Activity_Apply_Record.html", username=username, activitys=applyActivityList)


@app.route("/student_Social_Apply")
def student_Social_Apply():
    username = USERNAME
    now = datetime.datetime.now()
    time = now.strftime("%Y-%m-%d")
    return render_template("student_Social_Apply.html", time=time, username=username)


@app.route("/student_JoinSocial")
def student_JoinSocial():
    username = USERNAME
    # sql = "select id from user where username=%s"
    # cursor.execute(sql,username)
    # db.commit()
    # a = cursor.fetchone()
    # for i in cursor.fetchone():
    #     id = i
    sql = "select social. *  from social LEFT JOIN us ON social.id=Us.sID WHERE UID IS NULL"
    cursor.execute(sql)
    db.commit()
    socials = cursor.fetchall()
    socialList = []
    for social in socials:
        socialList.append(social)
    print(socialList)
    return render_template("student_JoinSocial.html", socials=socialList)


@app.route("/student_DeleteSocial_Register/<socialId>")
def student_DeleteSocial_Register(socialId):
    print(UID)
    sql = "delete from us where uid=%s and sid=%s"
    cursor.execute(sql, [UID, socialId])
    db.commit()
    return redirect("/student_MySocial")


@app.route("/student_JoinSocial_Register/<socialId>")
def student_JoinSocial_Register(socialId):
    print(UID)
    sql = "insert into us (uid,sid) values(%s,%s)"
    cursor.execute(sql, [UID, socialId])
    db.commit()
    return redirect("/student_JoinSocial")


@app.route("/student_MySocial")
def student_MySocial():
    # username = USERNAME
    # str = ""
    # List = []
    # belongList = []
    # print(username)
    # sql = 'select belong from user where username=%s'
    # cursor.execute(sql, username)
    # db.commit()
    # belongs = cursor.fetchall()
    # print(belongs)
    # for i in belongs:
    #     for o in i:
    #         str = o
    #         print(o)
    # print(str)
    # print(str.split(","))
    # List = str.split(",")
    # for i in List:
    #     print(i)
    #     sql = "select * from social where name = %s"
    #     cursor.execute(sql,i)
    #     db.commit()
    #     belongList.append(cursor.fetchone())
    # print(belongList)
    username = USERNAME

    sql = "select * from user where username = %s"
    cursor.execute(sql, username)
    db.commit()
    UID = cursor.fetchone()[0]
    print(UID)

    belongList = []
    sql = "select sid from us where UID = %s"
    cursor.execute(sql, UID)
    db.commit()
    for i in cursor.fetchall():
        print(i)
        sql = "select * from social where id = %s"
        cursor.execute(sql, i[0])
        db.commit()
        belongList.append(cursor.fetchone())
    print(belongList)
    return render_template("student_MySocial.html", belongs=belongList, username=username)


# 遍历没有报名的活动
@app.route("/ActivityRegistration")
def ActivityRegistration():
    # 遍历activity列表
    username = USERNAME
    print(username)
    activityList = []
    now = datetime.datetime.now()
    time = now.strftime("%Y-%m-%d")
    sql = f"select activity. *  from activity LEFT JOIN ua ON activity.activityId=UA.AID WHERE UID IS  NULL and activityEndTIME >='{time}'"
    cursor.execute(sql)
    db.commit()
    activitys = cursor.fetchall()
    for activity in activitys:
        activityList.append(activity)
    return render_template("student_Activity_Find.html", activitys=activityList, username=username)


# 点击报名的功能 刷新当前页面 显示 在报名时间内 且没有报名的活动
@app.route("/register/<activityId>")
def register(activityId):
    username = USERNAME
    print(USERNAME)
    # 把我的个人信息表添加到 活动信息表的 参与人员表中

    print(username)
    # 获得userId
    sql = "select * from user where username = %s"
    cursor.execute(sql, username)
    db.commit()
    user = cursor.fetchall()
    print(user)
    userId = user[0][0]
    sql = "insert into ua (UID,Aid) values(%s,%s)"
    cursor.execute(sql, [userId, activityId])
    db.commit()
    return redirect("/ActivityRegistration")


# 遍历可退出的活动
@app.route("/student_Activity_Running")
def student_Activity_Running():
    username = USERNAME
    print(username)
    activityList = []
    now = datetime.datetime.now()
    time = now.strftime("%Y-%m-%d")
    sql = f"select * from activity,ua where UA.AID=activity.activityId and activityEndTIME >='{time}'"
    print(sql)
    cursor.execute(sql)
    db.commit()
    activitys = cursor.fetchall()  # ((),(),())
    print(activitys)
    print(activitys)
    for activity in activitys:
        print(activity)
        activityList.append(activity)  # [(),(),()]
    # print(activityList)
    return render_template("student_Activity_Running.html", activitys=activityList, username=username)


# 退出报名的小功能
@app.route("/deleteRegistration/<activityId>")
def deleteRegistration(activityId):
    print(activityId)
    sql = "delete from ua where Aid = %s"
    cursor.execute(sql, activityId)
    db.commit()
    return redirect("/student_Activity_Running")


# 记录学生活动得分
@app.route("/student_Activity_Journal")
def student_Activity_Journal():
    username = USERNAME
    activityList = []
    print(username)
    now = datetime.datetime.now()
    time = now.strftime("%Y-%m-%d")
    sql = f"select * from activity where activityEndTime < '{time}'"
    cursor.execute(sql)
    db.commit()
    activitys = cursor.fetchall()
    credit = 0.0
    for activity in activitys:
        print(activity)
        num = float(activity[7])
        credit += num
        activityList.append(activity)  # [(),(),()]
    print(credit)

    sql = "update user set credit=%s where username=%s"
    cursor.execute(sql, [credit, username])
    db.commit()

    return render_template("student_Activity_Journal.html", username=username, activitys=activityList)


@app.route("/teacher_Email")
def teacher_Email():
    username = USERNAME
    return render_template("teacher_Email.html", username=username)


@app.route("/teacher_News")
def teacher_News():
    username = USERNAME
    return render_template("teacher_News.html", username=username)


if __name__ == '__main__':
    app.run(host="127.0.0.1", debug=True)
